<?php

require_once(SHMS_CORE_PATH."/shms_core_constants.inc");

/**
 * @file
 *  The database api, and its related function, comes to this file.
 */


/**
 * Check if the $var array contains the keys specified in the $fields array 
 * Currently designed this mainly for testing purpose
 */
function shms_core_db_make_sure_field_exists($var,$fields,$message="Default Message:"){
  foreach($fields as $key){
    if(!array_key_exists($key, $var)){
      throw new Exception($message." key $key doesn't exist.");
    }
  }
  return TRUE;
}


/**
 * Load the data from person table.
 * @param field: the field user want to query on
 * @param value: the value to be checked.
 * 
 * @return an array of the first record got from the query, or empty array if 
 *    there's no result.
 */
function shms_core_db_load_from_table($table_name,$field,$value){
  return db_select($table_name,'t')->orderBy($field,"DESC")->condition($field,$value)->fields('t')->execute()->fetchAssoc();
}

function shms_core_db_load_persons_by_blk($blk_letter = "all",$arr_key = "id"){
  $query = db_select(SHMS_PERSON_TABLE,'p');
  $query->join(SHMS_PERSON_BY_YEAR_TABLE,'py','p.id = py.pid');
  $query->fields('py',array('year','room_number'));
  $query->addField('py', 'id','pyid');
  $query->condition('py.year',shms_get_current_academic_year());
  $query->fields('p');
  $query->orderBy('p.full_name');
  if($blk_letter != 'all') $query->condition("py.room_number",$blk_letter."%","LIKE");
  return $query->execute()->fetchAllAssoc($arr_key,PDO::FETCH_ASSOC);
}

/**
 * @param $table
 *   - the name of the table being query
 * @param $field
 *   - the filed which condition is on
 * @param $value
 *   - the value which condition is on
 * @param $key
 *   - the key for the associative array
 * @param sortBy
 *   - if not empty, would sort the query based on : $sortBy['field'], $sortBy['dir']
 * @param secondT
 *   - if not empty, would join another table based on: $['table'],$['condition']
 */
function shms_core_db_load_assoc_all_match($table,$field,$value,$key,$sortBy = array(),$secondT = array()){
  $query = db_select($table,'t1')->fields('t1')->condition($field,$value);
  if(!empty($sortBy)) $query->orderBy($sortBy['field'],$sortBy['dir']);
  if(!empty($secondT)){
    $query->innerJoin($secondT['table'],'t2',$secondT['condition']);
    $query->fields('t2',$secondT['fields']);
  }
  return $query->execute()->fetchAllAssoc($key,PDO::FETCH_ASSOC);
}

